<?php
/**
 * @author Bart Hanssens <bart.hanssens@fedict.be>
 */

/**
 * Get database connection
 *
 * @return
 *  Database connection
 */
function sql_connection() {
	$host = 'localhost';
	$user = 'eyeballs';
	$pass = 'happy';
	$db = 'eyeballs';

	return mysqli_connect($host, $user, $pass, $db);
}

/**
 * Store information about current session in the database
 *
 * @param string $cookie
 *  Session cookie.
 * @param string $https
 *  HTTPS used or not
 * @param string $ip
 *  IP address of the browser.
 * @param string $forward
 *  Forward-for IP address (if any)
 * @param string $agent
 *  Browser identification string.
 */
function store_session($cookie, $https, $ip, $forward, $agent) {
	$sql = "INSERT INTO sessions(cookie, stamp, https, ip, forward, agent) " .
			"VALUES (?, NOW(), ?, ?, ?, ?) ";
	$mysql = sql_connection();

	$stmt = $mysql->prepare($sql);
	$stmt->bind_param("sssss", $cookie, $https, $ip, $forward, $agent);
	$stmt->execute();
	$stmt->close();

	$mysql->close();
}


/**
 * Get info on last 100 connections 
 *
 * @return array
 *  Array of connections 
 */
function list_all_connections() {
	$arr = array();

	$sql = "SELECT cookie, stamp, https, ip, forward, agent " .
			"FROM sessions ".
			"ORDER BY id DESC, cookie DESC ".
			"LIMIT 100";
	$mysql = sql_connection();

	$stmt = $mysql->prepare($sql);
	$stmt->bind_result($cookie, $stamp, $https, $ip, $forward, $agent);
	$stmt->execute();
	while ($stmt->fetch()) {
		$arr[] = array(
					'cookie' => $cookie,
					'stamp' => $stamp,
					'https' => $https,
					'ip' => $ip,
					'forward' => $forward,
					'agent' => $agent);
	}

	$stmt->close();
	$mysql->close();

	return $arr;
}


/**
 * Get info on last 100 connections for a cookie 
 *
 * @param string
 *  Cookie
 * @return array
 *  Array of connections 
 */
function list_cookie_connections($cookie) {
	$arr = array();

	$sql = "SELECT stamp, https, ip, forward, agent " .
			"FROM sessions ".
			"WHERE cookie = ? ".
			"ORDER BY id DESC ".
			"LIMIT 100";
	$mysql = sql_connection();

	$stmt = $mysql->prepare($sql);
	$stmt->bind_param("s", $cookie);
	$stmt->bind_result($stamp, $https, $ip, $forward, $agent);
	$stmt->execute();
	while ($stmt->fetch()) {
		$arr[] = array(
					'stamp' => $stamp,
					'https' => $https,
					'ip' => $ip,
					'forward' => $forward,
					'agent' => $agent);
	}

	$stmt->close();
	$mysql->close();

	return $arr;
}

/**
 * Get the list of cookies which have been sent over multiple IP addresses
 *
 * @return array
 */
function list_multiple_ips() {
	$arr = array();

	$sql =	"SELECT cookie, COUNT(DISTINCT ip), agent " .
		"FROM sessions " .
		"GROUP BY cookie " .
		"HAVING COUNT(DISTINCT ip) > 1 " .
		"ORDER BY MAX(id) DESC";

	$mysql = sql_connection();
	
	$stmt = $mysql->prepare($sql);
	$stmt->bind_result($cookie, $ip, $agent);
	$stmt->execute();
	while ($stmt->fetch()) {
		$arr[] = array(
			'cookie' => $cookie,
			'ip' => $ip,
			'agent' => $agent
		);
	}

	$stmt->close();
	$mysql->close();

	return $arr;
}
?>
